﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace Common
{
    public class SqlExption
    {
        private static SqlHelper DBHelper = new SqlHelper();

        public static T DataRowToModel<T>(DataRow row)
        {
            T t = Activator.CreateInstance<T>();
            foreach (var item in t.GetType().GetProperties())
            {
                if (row[item.Name] != null)
                {
                    if (item.PropertyType.Name == TypeCode.Int32.ToString())
                    {
                        if (row[item.Name] != DBNull.Value)
                        {
                            item.SetValue(t, Convert.ToInt32(row[item.Name]), null);
                        }
                        else
                        {
                            item.SetValue(t, 0, null);
                        }
                    }
                    else if (item.PropertyType.Name == TypeCode.Decimal.ToString())
                    {
                        if (row[item.Name]!=DBNull.Value)
                        {
                            item.SetValue(t, Convert.ToDecimal(row[item.Name]), null);
                        }
                        else
                        {
                            item.SetValue(t,Convert.ToDecimal(0), null);
                        }
                      
                    }
                    else if (item.PropertyType.Name == TypeCode.DateTime.ToString())
                    {
                        if (row[item.Name] != DBNull.Value)
                        {
                            item.SetValue(t, Convert.ToDateTime(row[item.Name]), null);
                        }
                        else
                        {
                            item.SetValue(t,DateTime.MinValue, null);
                        }
                    }
                    else
                    {
                        if (row[item.Name] != DBNull.Value)
                        {
                            item.SetValue(t, row[item.Name].ToString(), null);
                        }
                        else
                        {
                            item.SetValue(t, string.Empty, null);
                        }
                    }
                }

            }
            return t;
        }

        public static List<T> GetList<T>()
        {
            List<T> list = new List<T>();
            string sql = "select * from  " + typeof(T).Name + "";
            DataSet ds = DBHelper.Query(sql, null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    T t = DataRowToModel<T>(dr);
                    list.Add(t);
                }
            }
            return list;
        }

        public static int Add<T>(T t)
        {
            string sql = "INSERT INTO  " + typeof(T).Name + "";
            sql += " (";
            int i = 1;
            foreach (var item in t.GetType().GetProperties())
            {
                if (item.Name.Equals("Id"))
                {
                    continue;
                }
                if (i < t.GetType().GetProperties().Length - 1)
                {
                    sql += item.Name + ",";
                }
                else
                {
                    sql += item.Name;
                }
                i++;
            }
            sql += ")";
            sql += " VALUES ( ";
            int y = 1;
            foreach (var item in t.GetType().GetProperties())
            {
                if (item.Name.Equals("Id"))
                {
                    continue;
                }
                if (y < t.GetType().GetProperties().Length - 1)
                {
                    sql += "@" + item.Name + ",";
                }
                else
                {
                    sql += "@" + item.Name;
                }
                y++;
            }
            sql += ")";
            SqlParameter[] parameters = { };
            List<SqlParameter> list = parameters.ToList();
            foreach (var item in t.GetType().GetProperties())
            {
                if (item.Name.Equals("Id"))
                {
                    continue;
                }
                if (item.PropertyType.Name == TypeCode.Int32.ToString())
                {
                    list.Add(new SqlParameter("@" + item.Name + "", SqlDbType.Int, 50));
                }
                else if (item.PropertyType.Name == TypeCode.DateTime.ToString())
                {
                    list.Add(new SqlParameter("@" + item.Name + "", SqlDbType.DateTime));
                }
                else
                {
                    list.Add(new SqlParameter("@" + item.Name + "", SqlDbType.NVarChar, 4000));
                }

            }
            parameters = list.ToArray();
            int x = 0;
            foreach (var item in t.GetType().GetProperties())
            {
                if (item.Name.Equals("Id"))
                {
                    continue;
                }
                var d = Convert.ToDateTime("0001-01-01 00:00:00");
                var mind = Convert.ToDateTime("1753-01-01 12:00:00");
                var v = item.GetValue(t, null);
                if (item.PropertyType.Name == TypeCode.DateTime.ToString() && v.Equals(d))
                {
                    parameters[x].Value = mind;
                }
                else
                {
                    parameters[x].Value = item.GetValue(t, null);
                }
               // parameters[x].Value = item.GetValue(t, null);
                x++;
            }
            return DBHelper.ExecuteSql(sql, parameters);
        }

        public static int Update<T>(T t)
        {
            string sql = "UPDATE  " + typeof(T).Name + " SET ";
            int i = 1;
            foreach (var item in t.GetType().GetProperties())
            {
                if (item.Name.Equals("Id"))
                {
                    continue;
                }
                if (i < t.GetType().GetProperties().Length - 1)
                {
                    sql += item.Name + "=" + "@" + item.Name + ",";
                }
                else
                {
                    sql += item.Name + "=" + "@" + item.Name;
                }
                i++;
            }
            sql += " WHERE Id=@Id ";
            SqlParameter[] parameters = { };
            List<SqlParameter> list = parameters.ToList();
            foreach (var item in t.GetType().GetProperties())
            {

                if (item.PropertyType.Name == TypeCode.Int32.ToString())
                {
                    list.Add(new SqlParameter("@" + item.Name + "", SqlDbType.Int, 50));
                }
                else if (item.PropertyType.Name == TypeCode.DateTime.ToString())
                {
                    list.Add(new SqlParameter("@" + item.Name + "", SqlDbType.DateTime));
                }
                else
                {
                    list.Add(new SqlParameter("@" + item.Name + "", SqlDbType.NVarChar, 255));
                }

            }
            parameters = list.ToArray();
            int x = 0;
            foreach (var item in t.GetType().GetProperties())
            {
                //0001/1/1 星期一 上午 12:00:00
                //
                var d = Convert.ToDateTime("0001-01-01 00:00:00");
                var mind= Convert.ToDateTime("1753-01-01 12:00:00");
                var v = item.GetValue(t, null);
                if (item.PropertyType.Name == TypeCode.DateTime.ToString()&& v.Equals(d))
                {
                    parameters[x].Value = mind;
                }
                else
                {
                    parameters[x].Value = item.GetValue(t, null);
                }
               
                x++;
            }
            return DBHelper.ExecuteSql(sql, parameters);
        }

        public static int Delete<T>(int Id)
        {
            string sql = "DELETE FROM  " + typeof(T).Name + "";
            sql += " WHERE Id=@Id";

            SqlParameter[] parameters = {
                    new SqlParameter("@Id", SqlDbType.Int,50)
            };
            parameters[0].Value = Id;

            return DBHelper.ExecuteSql(sql, parameters);
        }
    }
}